--*----------------------------------------------------------------------------
--
-- GetImageLink
--
--*----------------------------------------------------------------------------
IF EXISTS (
  SELECT 1
    FROM sys.objects
   WHERE type = 'FN'
     AND schema_id = SCHEMA_ID('dbo')
     AND name = 'GetImageLink'
)
DROP FUNCTION GetImageLink
GO
--*----------------------------------------------------------------------------
CREATE FUNCTION dbo.GetImageLink
(
  @String NVARCHAR(MAX)
, @Size   VARCHAR(50)
)
RETURNS NVARCHAR(MAX)
BEGIN
  DECLARE @Result NVARCHAR(MAX) = LTRIM(RTRIM(@String))

  IF @Result LIKE '%photo.qip.ru%'
  BEGIN
    SET @Result = CASE @Size
                    WHEN 'small' THEN REPLACE(@Result,'[%size%]', 'small')
                    WHEN 'middle' THEN REPLACE(@Result,'[%size%]', 'middle')
                    WHEN 'large' THEN REPLACE(@Result,'[%size%]', 'large')
                    WHEN 'xlarge' THEN REPLACE(@Result,'[%size%]', 'xlarge')
                    ELSE @Result
                  END
  END
  ELSE
  IF @Result LIKE '%fotki.yandex.ru%'
  BEGIN
    SET @Result = CASE @Size
                    WHEN 'small' THEN REPLACE(@Result,'[%size%]', 'XS')
                    WHEN 'middle' THEN REPLACE(@Result,'[%size%]', 'M')
                    WHEN 'large' THEN REPLACE(@Result,'[%size%]', 'L')
                    WHEN 'xlarge' THEN REPLACE(@Result,'[%size%]', 'XL')
                    ELSE @Result
                  END
  END

  RETURN @Result
END
GO
--*----------------------------------------------------------------------------
ALTER TABLE ALBUM_ITEM ADD URL_SMALL AS (dbo.GetImageLink(URL, 'SMALL'))
GO
ALTER TABLE ALBUM_ITEM ADD URL_MIDDLE AS (dbo.GetImageLink(URL, 'MIDDLE'))
GO
ALTER TABLE ALBUM_ITEM ADD URL_LARGE AS (dbo.GetImageLink(URL, 'LARGE'))
GO
ALTER TABLE ALBUM_ITEM ADD URL_XLARGE AS (dbo.GetImageLink(URL, 'XLARGE'))
GO
